﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Runtime.InteropServices;


namespace sqllib
{
	class sqllib : IDisposable
	{
		private static string VERSION = "1.0.0.1";
		private bool DISPOSED = false;
		private IntPtr handle;

		private SQLiteConnection _conn;
		public enum DbState
		{
			Exception = -2,
			Broken = -1,
			Open = 1,
			Connecting = 1,
			Executing = 2,
			Fetching = 3,
			Closed = 4
		}
		
		private string _targetPath;//target sqlite database full directory path(ex:C:/AppDataBase/).
		private string _targetName;//target sqlite database full file name(ex:abc.sqlite).

		public sqllib()
		{
			this._targetPath = string.Empty;
			this._targetName = string.Empty;
			this._conn = new SQLiteConnection();
		}

		public sqllib(IntPtr handle)
        {
            this.handle = handle;
        }

		public sqllib(string as_path, string as_name)
		{
			this._targetPath = as_path;
			this._targetName = as_name;

			if (checkPath(as_path) && checkFile(as_path, as_name)) {
				this._conn = new SQLiteConnection(@"Data Source=" + as_path + as_name);
			} else {
				this._conn = new SQLiteConnection();
			}
		}

		public sqllib(string as_path, string as_name, bool ab_createDatabase)
		{
			this._targetPath = as_path;
			this._targetName = as_name;

			if (checkPath(as_path)) {
				if (checkFile(as_path, as_name))
					this._conn = new SQLiteConnection(@"Data Source=" + as_path + as_name);
			} else {
				if(ab_createDatabase)
					this._conn = new SQLiteConnection(@"Data Source=" + as_path + as_name);
			}
		}

		public void setFilePath(string as_path)
		{
			this._targetPath = as_path;
		}

		public void setFileName(string as_name)
		{
			this._targetName = as_name;
		}

		public void setConnectionString(string as_path, string as_name)
		{
			this._conn.ConnectionString = "@Data Source=" + as_path + as_name;
		}

		public string getFilePath()
		{
			return this._targetPath;
		}

		public string getFileName()
		{
			return this._targetName;
		}

		public string getConnectionString()
		{
			return this._conn.ConnectionString;
		}

		public DbState getConnectionStatus()
		{
			DbState newState = 0;
			switch(this._conn.State){
				case ConnectionState.Broken:
					newState = DbState.Broken;
					break;
				case ConnectionState.Closed:
					newState = DbState.Closed;
					break;
				case ConnectionState.Connecting:
					newState = DbState.Connecting;
					break;
				case ConnectionState.Executing:
					newState = DbState.Executing;
					break;
				case ConnectionState.Fetching:
					newState = DbState.Fetching;
					break;
				case ConnectionState.Open:
					newState = DbState.Open;
					break;
				default:
					newState = DbState.Exception;
					break;
			}
			return newState;
		}

		public static string getVersion()
		{
			return VERSION;
		}

		public int ExcuteSingleTransactQuery(string as_sql,List<SQLiteParameter> alist_sqlparam)
		{
			int li_Row = -1;
			try {
				SQLiteCommand sqlcom = new SQLiteCommand(as_sql, this._conn);

				if (alist_sqlparam != null) {
					if (alist_sqlparam.Count > 0) {
						sqlcom.Parameters.AddRange(alist_sqlparam.ToArray());
					}
				}

				li_Row = sqlcom.ExecuteNonQuery();
				sqlcom.Dispose();
			} catch{
				li_Row = -2;
			}

			return li_Row;
		}

		public int ExcuteMultiTransactQuery(string as_sql, List<List<SQLiteParameter>> alist_sqlparam)
		{
			int li_Row = -1;
			try {
				SQLiteCommand sqlcom = new SQLiteCommand(as_sql, this._conn);

				if (alist_sqlparam != null) {
					if (alist_sqlparam.Count > 0) {
						li_Row = 0;

						foreach (List<SQLiteParameter> list_param in alist_sqlparam) {
							sqlcom.Parameters.Clear();
							sqlcom.Parameters.AddRange(list_param.ToArray());
							li_Row += sqlcom.ExecuteNonQuery();
						}
					}
				}
				sqlcom.Dispose();
			} catch {
				li_Row = -2;
			}

			return li_Row;
		}

		public object ExcuteSelectQueryGetFirst(string as_sql,List<SQLiteParameter> alist_sqlparam)
		{
			object lo_result = null;
			try {
				SQLiteCommand sqlcom = new SQLiteCommand(as_sql, this._conn);

				if (alist_sqlparam != null) {
					if (alist_sqlparam.Count > 0) {
						sqlcom.Parameters.AddRange(alist_sqlparam.ToArray());
					}
				}

				lo_result = sqlcom.ExecuteScalar();
				sqlcom.Dispose();
			} catch{
				lo_result = null;
			}

			return lo_result;
		}

		public List<object[]> ExcuteSelectQuery(string as_sql, List<SQLiteParameter> alist_sqlparam)
		{
			List<object[]> list_object = new List<object[]>();
			try {
				SQLiteCommand sqlcom = new SQLiteCommand(as_sql, this._conn);

				if (alist_sqlparam != null) {
					if (alist_sqlparam.Count > 0) {
						sqlcom.Parameters.AddRange(alist_sqlparam.ToArray());
					}
				}

				SQLiteDataReader sqlreader = sqlcom.ExecuteReader();

				if (sqlreader.HasRows) {
					while (sqlreader.Read()) {
						object[] aro_obj = null;
						sqlreader.GetValues(aro_obj);
						list_object.Add(aro_obj);
					}
				}

				sqlcom.Dispose();
			} catch {
				list_object = null;
			}
			return list_object;
		}

		private bool checkPath(string as_path)
		{
			return Directory.Exists(as_path);
		}

		private bool checkFile(string as_path, string as_name)
		{
			return File.Exists(as_path + as_name);
		}

		public void openDatabase()
		{
			this._conn.Open();
		}

		public void closeDataBase()
		{
			this._conn.Close();
		}

		public void Dispose()
		{
			Dispose(true);
			GC.SuppressFinalize(this);
		}

		private void Dispose(bool disposing)
		{
			if (!this.DISPOSED) {
				if (disposing) {
					this._conn.Dispose();
				}
				CloseHandle(handle);
				handle = IntPtr.Zero;
				DISPOSED = true;
			}
		}

		[DllImport("Kernel32")]
		[return: MarshalAs(UnmanagedType.Bool)]
		private extern static Boolean CloseHandle(IntPtr handle);
		~sqllib()
        {
            Dispose(false);
        }
	}
}
